require "rubygems"
require "mysql"

planets = File.open("planets.csv", "r")
system_id = nil
dbh = Mysql.real_connect("localhost", "root", "rational", "exo_development")
insert_body = "INSERT INTO planets ( system_id, name, period, mass, reference, semi_major_axis, eccentricity) VALUES "

planets.each do |planet|
  planet = planet.split(",")
  system_name = planet[1].split("\s")
  planet_name = system_name.pop()
  if system_name[0] == "HD"
    system = dbh.query("SELECT id FROM systems WHERE hd_number = #{system_name[1]}")
    system.each do |sys|
      system_id = sys
    end
  elsif system_name[0] == "HIP"
    system = dbh.query("SELECT id FROM systems WHERE hip_number = #{system_name[1]}")
    system.each do |sys|
      system_id = sys
    end
  else
    system_name = system_name.join("\s")
    system = dbh.query("SELECT id FROM systems WHERE name = \'#{system_name}\'")
    system.each do |sys|
      system_id = sys
    end
  end
  ref = planet[-1].chomp
  ref = ref.gsub(/\'/,'\\'+'\'')
  p ref
  p "(#{system_id[0].to_i}, 1, \'#{planet_name}\', #{planet[2].to_f}, #{planet[8].to_f}, \'#{ref}\')"
  dbh.query(insert_body + "(#{system_id[0].to_i}, \'#{planet_name}\', #{planet[2].to_f}, #{planet[8].to_f/1047.56}, \'#{ref}\', #{planet[9].to_f}, #{planet[4].to_f} )")
end

